package vn.bus.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import vn.bus.dto.StationDTO;

public class StationDAO {

	public ArrayList<StationDTO> getAllStations(Connection connection) throws Exception
	{
		ArrayList<StationDTO> allStations = new ArrayList<StationDTO>();
		try
		{
			PreparedStatement ps = connection.prepareStatement("SELECT * FROM stations");
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				StationDTO Station = new StationDTO();
				Station.setId(rs.getString("id"));
				Station.setLat(rs.getDouble("lat"));
				Station.setLon(rs.getDouble("lon"));
				Station.setName(rs.getString("name"));
				allStations.add(Station);
			}
			return allStations;
		}
		catch(Exception e)
		{
			throw e;
		}
	}
		
	public ArrayList<StationDTO> textSearch(Connection connection, String str) throws Exception
	{
		ArrayList<StationDTO> textSearchStations = new ArrayList<StationDTO>();
		try
		{
			String qr = "SELECT * FROM `stations` WHERE `name` LIKE " + "'%" + str + "%'";
			PreparedStatement ps = connection.prepareStatement(qr);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				StationDTO Station = new StationDTO();
				Station.setId(rs.getString("id"));
				Station.setLat(rs.getDouble("lat"));
				Station.setLon(rs.getDouble("lon"));
				Station.setName(rs.getString("name"));
				textSearchStations.add(Station);
			}
			return textSearchStations;
		}
		catch(Exception e)
		{
			throw e;
		}
	}
	
	public ArrayList<StationDTO> clickSearch(Connection connection, double lat, double lon) throws Exception
	{
		ArrayList<StationDTO> clickSearchStations = new ArrayList<StationDTO>();
		try
		{
			// 300m
			double u = lat + 0.0027, d = lat - 0.0027, l = lon - 0.00275, r = lon + 0.00275;
			// SELECT * FROM stations WHERE lat >= $d AND lat <= $u AND lon >= $l AND lon <= $r
			String qr = "SELECT * FROM stations WHERE lat >= " + d + " AND lat <= " + u
					+ " AND lon >= " + l + " AND lon <= " + r;
			PreparedStatement ps = connection.prepareStatement(qr);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				StationDTO Station = new StationDTO();
				Station.setId(rs.getString("id"));
				Station.setLat(rs.getDouble("lat"));
				Station.setLon(rs.getDouble("lon"));
				Station.setName(rs.getString("name"));
				clickSearchStations.add(Station);
			}
			return clickSearchStations;
		}
		catch(Exception e)
		{
			throw e;
		}
	}
	
}
